import json
import redis  # pip install redis
import pymysql

def main():
    # 指定redis数据库信息
    rediscli = redis.StrictRedis(host='localhost', port = 6379, db = 1,password='123456')
    # 指定mysql数据库
    mysqlcli = pymysql.connect(host='127.0.0.1', user='root', passwd='123456', db='temp', charset='utf8')
    # 无限循环
    while True:
        source, data = rediscli.blpop(["sina:items"]) # 从redis里提取数据
        item = json.loads(data.decode('utf-8')) # 把 json转字典

        try:
            # 使用cursor()方法获取操作游标
            cur = mysqlcli.cursor()
            # 使用execute方法执行SQL INSERT语句
            sql = 'insert into sina_weibo(username,userid,verified_reason,followers_count,follow_count) ' \
                  'VALUES(%s,%s,%s,%s,%s)'
            cur.execute(sql, (item['username'],item['userid'],item['verified_reason'],item['followers_count'],item['follow_count']))
            # 提交sql事务
            mysqlcli.commit()
            #关闭本次操作
            cur.close()
            print ("插入 %s" % item['username'])
        except pymysql.Error as e:
            mysqlcli.rollback()
            print ("插入错误" ,str(e))

if __name__ == '__main__':
    main()